import os, sys
import requests
import json

from numpy.f2py.auxfuncs import throw_error

# 导入其他模块文件
project_path = os.path.abspath(os.path.join(os.path.dirname(__file__), "../database"))
if project_path not in sys.path:
    sys.path.append(project_path)

import database.database_mysql as db
import akshare as ak
import datetime
import utils.date_util as dt
import pandas as pd
import numpy as np

# 股票
# 总市值 = 总股本 * 股价
# 流通市值 = 流通股本 * 股价
# 市净率也叫“市账率”，是普通股每股市价与每股净资产的比率，反映普通股股东愿意为每1元净资产支付的价格，也反映了市场对公司净资产质量的评价。
# 市净率的计算公式为：市净率＝每股市价÷每股净资产。其中：每股净资产＝普通股股东权益÷流通在外普通股股数。
# 市盈率是普通股每股市价与每股收益的比率，反映普通股股东愿意为每1元净利润支付的价格。
# 市盈率的计算公式为：市盈率＝每股市价÷每股收益。其中：每股收益=普通股股东净利润÷流通在外普通股加权平均股数。
class Main:

    def __init__(self):
        # self.today = datetime.date.today()
        # stock_board_industry_columns = ['代码', '名称', '最新价', '涨跌幅', '涨跌额', '成交量', '成交额', '振幅', '最高',
        #                              '最低', '今开', '昨收', '量比', '换手率', '市盈率-动态', '市净率', '总市值', '流通市值',
        #                              '涨速', '5分钟涨跌', '60日涨跌幅', '年初至今涨跌幅']
        self.stock_board_industry_columns = ['code', 'name', 'zx_price', 'zd_range', 'zd_amt', 'cj_num', 'cj_amt',
                                          'zf', 'highest', 'lowest', 'jk', 'zs', 'lb', 'hs_rate', 'per', 'pbr', 'zsz',
                                          'ltsz', 'zs', 'zd_5', 'zd_60', 'zd_curr_year']

        # self.stock_board_industry_hist_columns = ['日期', '开盘', '收盘', '最高', '最低', '涨跌幅', '涨跌额', '成交量', '成交额',
        #                                        '振幅','换手率']
        self.stock_board_industry_hist_columns = ['dt', 'kp', 'sp', 'highest', 'lowest', 'zd_range', 'zd_amt',
                                                  'cj_num', 'cj_amt', 'zf', 'hs_rate']

        # self.stock_hist_columns = ['股票代码', '日期', '开盘', '收盘', '最高', '最低', '成交量', '成交额', '振幅', '涨跌幅',
        #                         '涨跌额', '换手率']
        self.stock_hist_columns = ['name', 'dt', 'kp', 'sp', 'highest', 'lowest', 'cj_num', 'cj_amt', 'zf', 'zd_range',
                                'zd_amt','hs_rate']


    # 实时行情数据 - 沪深京A股
    # 描述: 东方财富网 - 沪深京A股 - 实时行情数据
    def stock_spot_data(
            self,
            type: str
    ) -> pd.DataFrame:
        stock_spot_df = None
        if type == 'ALL':
            stock_spot_df = ak.stock_zh_a_spot_em()
        elif type == 'SH':
            stock_spot_df = ak.stock_sh_a_spot_em()
        elif type == 'SZ':
            stock_spot_df = ak.stock_sz_a_spot_em()
        else:
            throw_error('Error')
        return stock_spot_df


    # 东方财富-行业板块
    def stock_board_industry_data(
            self,
            file_path: str,
            file_name: str = 'stock_industry_data.xlsx'
    ) -> pd.DataFrame:
        if os.path.exists(file_path + file_name):
            stock_data = pd.read_excel(file_path + file_name, converters={'板块名称':str})
            # stock_data.columns = self.stock_data_columns
            return stock_data

        stock_board_industry_name_em_df = ak.stock_board_industry_name_em()
        stock_board_industry_name_em_df.to_excel(file_path + file_name, index=False)
        # stock_board_industry_name_em_df.columns = self.stock_data_columns
        return stock_board_industry_name_em_df

    # 东方财富-行业板块-成份股
    def stock_board_industry_cons_data(
            self,
            file_path: str = '',
            file_name: str = 'stock_board_industry_cons_data.csv',
            symbol: str = '小金属'
    ) -> pd.DataFrame:
        # if os.path.exists(file_path + file_name):
        #     stock_data = pd.read_excel(file_path + file_name, converters={'代码':str})
        #     stock_data.columns = self.stock_board_industry_hist_columns
        #     return stock_data

        stock_board_industry_cons_em_df = ak.stock_board_industry_cons_em(symbol=symbol)
        # print(stock_board_industry_cons_em_df)
        # stock_board_industry_cons_em_df.to_excel(file_path + file_name, index=False)
        # stock_board_industry_name_em_df.columns = self.stock_data_columns
        stock_board_industry_cons_em_df = stock_board_industry_cons_em_df.iloc[:, 1:3]
        stock_board_industry_cons_em_df.columns = ['code', 'stock_name']
        stock_board_industry_cons_em_df['indu_name'] = symbol
        return stock_board_industry_cons_em_df

    # 东方财富-行业板块-历史行情数据
    def stock_board_industry_hist_data(
            self,
            file_path: str,
            file_name: str = 'stock_industry_hist_data.xlsx',
            symbol: str = "小金属",
            start_date: str = "20240101",
            end_date: str = "20240401",
            period: str = "日k",
            adjust: str = "",
    ) -> pd.DataFrame:
        # if os.path.exists(file_path + file_name):
        #     stock_data = pd.read_excel(file_path + file_name, converters={'代码':str})
        #     stock_data.columns = self.stock_board_industry_hist_columns
        #     return stock_data
        # print(symbol, type(symbol))
        stock_board_industry_hist_em_df = ak.stock_board_industry_hist_em(symbol=symbol, start_date=start_date,
                                                                          end_date=end_date, period=period, adjust=adjust)
        # stock_board_industry_hist_em_df.to_excel(file_path + file_name, index=False)
        stock_board_industry_hist_em_df.columns = self.stock_board_industry_hist_columns
        stock_board_industry_hist_em_df['type'] = '01'
        stock_board_industry_hist_em_df['name'] = symbol
        # print(stock_board_industry_hist_em_df)
        return stock_board_industry_hist_em_df

    # 东方财富-沪深京 A 股日频率数据; 历史数据按日频率更新, 当日收盘价请在收盘后获取
    def stock_hist_data(
            self,
            file_path: str,
            file_name: str = 'stock_board_industry_cons_data.csv',
            symbol: str = "000001",
            period: str = "daily",
            start_date: str = "19700101",
            end_date: str = "20500101",
            adjust: str = "",
    ) -> pd.DataFrame:
        if os.path.exists(file_path + file_name):
            stock_data = pd.read_excel(file_path + file_name, converters={'代码': str})
            # stock_data.columns = self.stock_data_columns
            return stock_data

        # stock_zh_a_hist_df = ak.stock_zh_a_hist(symbol="000001", period="daily", start_date="20170301",
        #                                         end_date='20240528', adjust="")
        stock_zh_a_hist_df = ak.stock_zh_a_hist(symbol=symbol, period=period, start_date=start_date,
                                                end_date=end_date, adjust=adjust)
        print(stock_zh_a_hist_df)
        stock_zh_a_hist_df.to_excel(file_path + file_name, index=False)
        # stock_zh_a_hist_df.columns = self.stock_data_columns
        return stock_zh_a_hist_df


# 行情数据 - 沪深京A股
# 描述: 上证 - 沪深京A股 - 实时行情数据
def stock_info_sh_details(symbol: str = "600000") -> pd.DataFrame:
    """
    上海证券交易所-股票详情
    https://www.sse.com.cn/assortment/stock/list/info/company/index.shtml?COMPANY_CODE=600000
    :param symbol: 股票代码
    :type symbol: str
    :return: 指定 indicator 的数据
    :rtype: pandas.DataFrame
    """
    url = "https://query.sse.com.cn/commonQuery.do?jsonCallBack=jsonpCallback31305363&sqlId=COMMON_SSE_CP_GPJCTPZ_GPLB_CJGK_MRGK_C&TX_DATE=&TX_DATE_MON=&TX_DATE_YEAR=&_=1731753481149&SEC_CODE=" + symbol
    headers = {
        "Host": "query.sse.com.cn",
        "Pragma": "no-cache",
        "Referer": "https://www.sse.com.cn",
        "User-Agent": "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) "
        "Chrome/81.0.4044.138 Safari/537.36",
    }
    r = requests.get(url, headers=headers)
    # data_json = r.json()
    data_json = json.loads(r.text[22 : -1])
    # data_json = r.text
    temp_df = pd.DataFrame(data_json["result"])
    # print(temp_df)
    temp_df['TOTAL_VALUE'] = temp_df['TOTAL_VALUE'].astype(float) * 10000
    temp_df['NEGO_VALUE'] = temp_df['NEGO_VALUE'].astype(float) * 10000
    temp_df['CLOSE_PRICE'] = temp_df['CLOSE_PRICE'].astype(float)
    temp_df['TOTAL_CAPITAL'] = temp_df['TOTAL_VALUE'] / temp_df['CLOSE_PRICE']
    temp_df['NEGO_CAPITAL'] = temp_df['NEGO_VALUE'] / temp_df['CLOSE_PRICE']
    temp_df['code'] = symbol
    temp_df['name'] = temp_df['SEC_NAME']
    temp_df['type'] = 'SH'
    return temp_df[['TOTAL_CAPITAL', 'NEGO_CAPITAL', 'code', 'name', 'type']]

def stock_yjbb_data(date: str = '20240930') -> pd.DataFrame:
    """
    东方财富-数据中心-年报季报-业绩报表
    :param date: 年报季报
    :type date: str
    :return: 指定 indicator 的数据
    :rtype: pandas.DataFrame
    """
    stock_yjbb_em_df = ak.stock_yjbb_em(date=date)
    df = stock_yjbb_em_df.drop(columns=['序号'])
    df['股票代码'] = df['股票代码'].astype(str)
    df = df[df['股票代码'].str.startswith(('0', '3', '6'))]
    # df['每股收益'] = df['每股收益'].fillna(-9999)
    # df = df.query('每股收益 != -9999')
    df = df.replace(np.nan, '')
    df.columns = ['code', 'name', 'eps', 'income', 'income_tb', 'income_hb', 'net_profits', 'net_profits_tb',
                  'net_profits_hb', 'navps','roe', 'epcf', 'sgpm','industry', 'report_dt']
    df['dt'] = date
    return df


def stock_his_data(
        symbol: str = '000001',
        period: str = 'daily',
        start_date: str = '20240101',
        end_date: str = '20240930',
        adjust: str = '',
) -> pd.DataFrame:
    """
    东方财富网-行情首页-沪深京 A 股-每日行情
    https://quote.eastmoney.com/concept/sh603777.html?from=classic
    :param symbol: 股票代码
    :type symbol: str
    :param period: choice of {'daily', 'weekly', 'monthly'}
    :type period: str
    :param start_date: 开始日期
    :type start_date: str
    :param end_date: 结束日期
    :type end_date: str
    :param adjust: choice of {"qfq": "前复权", "hfq": "后复权", "": "不复权"}
    :type adjust: str
    :return: 每日行情
    :rtype: pandas.DataFrame
    """
    stock_zh_a_hist_df = ak.stock_zh_a_hist(symbol=symbol, period=period, start_date=start_date, end_date=end_date,
                                            adjust=adjust)
    # print(stock_zh_a_hist_df)
    # df = stock_zh_a_hist_df.drop(columns=['序号'])
    # df['股票代码'] = df['股票代码'].astype(str)
    # df = df[df['股票代码'].str.startswith(('0', '3', '6'))]
    # df = df.replace(np.nan, '')
    # df.columns = ['code', 'name', 'eps', 'income', 'income_tb', 'income_hb', 'net_profits', 'net_profits_tb',
    #               'net_profits_hb', 'navps','roe', 'epcf', 'sgpm','industry', 'report_dt']
    # df['dt'] = date
    stock_zh_a_hist_df.columns = ['dt', 'code', 'kp', 'sp', 'highest', 'lowest', 'cj_num', 'cj_amt', 'zf', 'zd_range', 'zd_amt', 'hs_rate']
    # stock_zh_a_hist_df['cj_avg'] = stock_zh_a_hist_df['cj_avg']
    # cj_avg = stock_zh_a_hist_df['cj_num'].mean()
    # cj_last = stock_zh_a_hist_df.iloc[4:5, :]
    # print(stock_zh_a_hist_df['cj_num'])
    # print(cj_avg)
    # print(cj_last['cj_num'])
    # stock_zh_a_hist_df['cj_avg'] = cj_avg
    # stock_zh_a_hist_df['cj_num'] = cj_last['cj_num']
    # print(stock_zh_a_hist_df)
    return stock_zh_a_hist_df


def stock_hist_min_data(
        symbol: str = '000001',
        period: str = '1',
        start_date: str = '2024-12-12 09:30:00',
        end_date: str = '2024-12-12 15:00:00',
        adjust: str = '',
) -> pd.DataFrame:
    """
    东方财富网-行情首页-沪深京 A 股-每日分时行情; 该接口只能获取近期的分时数据，注意时间周期的设置
    https://quote.eastmoney.com/concept/sh603777.html?from=classic
    :param symbol: 股票代码
    :type symbol: str
    :param period: choice of {'1', '5', '15', '30', '60'}
    :type period: str
    :param start_date: 开始日期时间 2024-03-20 09:30:00
    :type start_date: str
    :param end_date: 结束日期时间 2024-03-20 15:00:00
    :type end_date: str
    :param adjust: choice of {"qfq": "前复权", "hfq": "后复权", "": "不复权"}
    :type adjust: str
    :return: 每日行情
    :rtype: pandas.DataFrame
    """

    stock_zh_a_hist_min_em_df = ak.stock_zh_a_hist_min_em(symbol=symbol, start_date=start_date,
                                                          end_date=end_date, period=period, adjust=adjust)
    # print(stock_zh_a_hist_min_em_df)
    stock_zh_a_hist_min_em_df.to_excel(os.path.dirname(os.getcwd()) + '/file/data/stock_zh_a_hist_min_em_df.xlsx', index=False)
    return stock_zh_a_hist_min_em_df


# stock_hist_min_data(period='5')

# df = pd.read_excel(os.path.dirname(os.getcwd()) + '/file/data/stock.xlsx')
# # 阈值
# threshold = 0.2
#
# # 使用shift和cumsum找到连续区间
# df['group_id'] = (df['涨跌幅'] > threshold).groupby(df['涨跌幅'].shift().fillna(0) > threshold).cumsum()
# df['group_id'] = df['group_id'].fillna(0)
#
# # 筛选出满足条件的区间
# result = df[df['涨跌幅'] > threshold].groupby('group_id').agg({'涨跌幅': 'min', 'group_id': 'count'})
# result.columns = ['start', 'length']
#
# print(result)

# df = pd.DataFrame({'value': [1, 2, 3, 10, 11, 12, 4, 5, 6, 7, 20, 21, 22]})
#
# # 阈值
# threshold = 10
#
# # 使用shift和cumsum找到连续区间
# df['group_id'] = (df['value'] > threshold).groupby(df['value'].shift().fillna(0) > threshold).cumsum()
# df['group_id'] = df['group_id'].fillna(0)
#
# # 筛选出满足条件的区间
# result = df[df['value'] > threshold].groupby('group_id').agg({'value': 'min', 'group_id': 'count'})
# result.columns = ['start', 'length']
#
# print(result)


# file_path = os.path.dirname(os.getcwd()) + '/file/data/'
# file_path = os.path.dirname(os.getcwd()) + '/file/config/sql/'
# file_name = 'data.sql'
# print(file_path+file_name)
# curr_date = dt.DateUtil().get_date_strftime(strftime='%Y%m%d')
# curr_date = '20241116'
# print(curr_date)
# pd.read_sql()

# main = Main()
# stock_data = stock_yjbb_data(date = '20230930')
# stock_data = pd.read_excel(file_path + file_name, converters={'A股代码' : str})
#
# stock_dict = stock_data[['A股代码', 'A股简称', 'A股总股本', 'A股流通股本']]
# print(stock_dict)
# stock_dict.columns = ["code", "name","total_capital","nego_capital"]
# stock_dict = stock_dict.iloc[:3, :]
# stock_dict.columns = ['code', 'name']
# stock_dict['type'] = 'SZ'
# print(stock_dict.iloc[:2, :])
# print(type(stock_dict['total_capital'][1]))

# test = db.MySQLHandler()
# test.insert_batch_df(df=stock_data, table_name='stock_yjbb')
# stock_dict['板块名称'].apply(lambda x : main.stock_board_industry_hist_data(file_path=file_path, symbol = x, end_date=curr_date))
# stock_data['A股代码'].apply(lambda x : test.insert_batch_df(df=stock_yjbb_data(), table_name='stock_yjbb', symbol=x))

# with open(file_path+file_name, 'r') as file:
#     sql = file.read()

# sql = f"SELECT a.CODE, b.`name`, b.total_capital, b.nego_capital, b.type FROM stock_industry_cons a LEFT JOIN stock_dict b ON a.CODE = b.CODE WHERE a.indu_name = '互联网服务' AND b.`name` IS NOT NULL"

# data = test.fetch_one(sql)
# data = test.fetch_all(sql)
# df = pd.DataFrame(data).iloc[:1]
# df = pd.DataFrame(data)
# df.columns = ['code', 'name', 'type', 'sp', 'dt', 'total_value', 'nego_value', 'income', 'net_profits', 'eps', 'navps',
#               'pe', 'pe1', 'pe2', 'pe3', 'pb']
# print(df)
# df.apply(lambda x : print(x))
# stock_data = df.apply(lambda x : stock_his_data(symbol=x['code'], name=x['code'], end_date=curr_date, adjust='qfq'))
# print(stock_data)

# for index, row in df.iterrows():
#     print(row['code'], row['name'])
#     stock_data = stock_his_data(symbol=row['code'], end_date=curr_date, adjust='qfq')
#     stock_data['name'] = row['name']
#     stock_data['type'] = row['type']
#     print(stock_data)
#     test.insert_batch_df(df=stock_data, table_name='stock_his')
